package com.ufo.store.management.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ufo.store.management.utils.Utils;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by taoli on 2016/4/12.
 */
public class HistoryDatabaseHelper extends SQLiteOpenHelper {
    // 数据库名称
    private static String DB_NAME = "store_history";
    // 数据库版本
    private static int DB_VERSION = 1;
    public SQLiteDatabase db;
    private static HistoryDatabaseHelper dataHelper = null;
    private Context mContext = null;
    /**
     * 第一次实例化时调用
     *
     * @return
     */
    public synchronized static HistoryDatabaseHelper getInstance(Context context) {
        if (dataHelper == null) {
            dataHelper = new HistoryDatabaseHelper(context);
        }
        return dataHelper;
    }

    /**
     * @param context context
     * @brief 构造函数
     */
    public HistoryDatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        this.mContext = context;
        // 只有调用了DatabaseHelper的getWritableDatabase()方法或者getReadableDatabase()方法之后，才会创建或打开一个连接
        //SQLiteDatabase sqliteDatabase = sQLiteHelper.getReadableDatabase();
        db = getWritableDatabase();
    }

    /**
     * 当第一次创建数据库的时候，调用该方法
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建isszmbalv3商品表
        db.execSQL("create table " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME
                + " (" + DatabaseManager.LOCAL_ISSZMBALV3_t_bd_item_info_TABLE_NAME_PRIMARY + " integer primary key,"
                + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + " char(20),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + " varchar(40),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + " varchar(20),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + " char(20),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + " varchar(10),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_price + " numeric(16, 4),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + " numeric(16, 4),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + " char(1),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + " char(4),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + " char(4),"
                + DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + " char(1)"
                + ")");
        // 创建isszmbalv3会员表
        db.execSQL("create table " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME
                + " (" + DatabaseManager.LOCAL_ISSZMBALV3_t_rm_vip_info_TABLE_NAME_PRIMARY + " integer primary key,"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + " char(2),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + " varchar(20),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + " varchar(20),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + " varchar(8),"
                + DatabaseManager.MEMBER_NAME_SPELL + " varchar(30),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + " char(2),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + " varchar(20),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + " varchar(30),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + " varchar(20),"
                + DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + " int,"            //消费次数
                + DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + " numeric(16, 4),"   //消费总额
                + DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + " numeric(10, 2),"      //累计积分
                + DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + " numeric(10, 2),"      //已用积分
                + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + " datetime,"     //vip开始时间
                + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + " datetime,"       //vip结束时间
                + DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + " datetime,"     //储蓄开始时间
                + DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date + " datetime"        //储蓄结束时间
                + ")");
        // 创建输入历史
        db.execSQL("create table " + DatabaseManager.INPUT_HISTORY_TABLE_NAME
                + " (" + DatabaseManager.INPUT_HISTORY_TABLE_NAME_PRIMARY + " integer primary key,"
                + DatabaseManager.INPUT_HISTORY_NAME + " char(20)"
                + ")");
    }

    /**
     * 当更新数据库的时候执行该方法
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //输出更新数据库的日志信息

    }

    /**
     * 关闭数据库
     *
     * @return 无
     */
    public void close() {
        db.close();
    }

    /**
     * 添加商品
     */
    public int insertIsszmbalv3Stock(List<ContentValues> initialValuesList) {
        int row = -1;
        for (ContentValues values : initialValuesList) {
            try {
                db.execSQL(
                        "INSERT INTO "
                                + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME
                                + "(" + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_price + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + ","
                                + DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + ") VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                        new Object[]{values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_rem),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_price),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_price),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id),
                                values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag)});
                if (row == -1)
                    row = 0;
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (row >= 0)
                row = row + 1;
        }
        return row;
    }

    /**
     * 添加商品
     */
    public int insertIsszmbalv3Stock(ContentValues values) {
        int row = -1;
        try {
            db.execSQL(
                    "INSERT INTO "
                            + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME
                            + "(" + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_price + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + ","
                            + DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + ") VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                    new Object[]{values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_rem),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_price),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_price),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id),
                            values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag)});
            row = 1;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return row;
    }

    public int deleteIsszmbalv3Stock(List<ContentValues> initialValuesList) {
        int row = -1;
        for (ContentValues values : initialValuesList) {
            try {
                db.execSQL(
                        "DELETE FROM "
                                + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME
                                + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "  = '" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no) + "'");
                if(row == -1 )
                    row = 0;
            } catch (Exception e) {
                e.printStackTrace();
            }
            if(row >= 0)
                row = row + 1;
        }

        return row;
    }

    public int deleteIsszmbalv3Stock(ContentValues values) {
        int row = -1;
        try {
            db.execSQL(
                    "DELETE FROM "
                            + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME
                            + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "  = '" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no) + "'");
            row = 1;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return row;
    }

    /**
     * 获取所有商品
     */
    public Cursor getAllIsszmbalv3Stock() {
        Cursor cursor = null;
        try {
            String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag +
                    " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " LIMIT " + 0 + "," + 5;
            cursor = db.rawQuery(sql, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (cursor != null)
            cursor.moveToFirst();
        return cursor;
    }

    /**
     * 添加会员
     */
    public int insertIsszmbalv3Member(ContentValues values) {
        int row = -1;
        try {
            String nameStr = (String)values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name);
            String str = "#";
            if (nameStr.length() != 0)
                str = Utils.cn2FirstSpell(nameStr).substring(0, 1);
            if (str.matches("[A-Z]")) {

            } else {
                str = "#";
            }

            db.execSQL(
                    "INSERT INTO "
                            + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME
                            + "(" + DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + "," +
                            DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date + "," +
                            DatabaseManager.MEMBER_NAME_SPELL + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    new Object[]{values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_type),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_id),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_no),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_mobile),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date),
                            values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date),
                            str});
            row = 1;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return row;
    }

    public int deleteIsszmbalv3Member(ContentValues values) {
        int row = -1;
        try {
            db.execSQL(
                    "DELETE FROM "
                            + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME
                            + " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "  = '" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_id) + "'");
            row = 1;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return row;
    }

    /**
     * 获取所有会员
     */
    public Cursor getAllIsszmbalv3Member() {
        Cursor cursor = null;
        try {
            String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date + "," +
                    DatabaseManager.MEMBER_NAME_SPELL +
                    " FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " LIMIT " + 0 + "," + 5;
            cursor = db.rawQuery(sql, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    public void deleteAllIsszmbalv3Stocks(){
        db.delete(DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME,null,null);
    }

    public void deleteAllIsszmbalv3Members(){
        db.delete(DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME,null,null);
    }



    /**
     * 以下是搜索历史记录
     */
    public int insertInputHistory(String strInput) {
        int row = -1;
        try {
            db.execSQL(
                    "INSERT INTO "
                            + DatabaseManager.INPUT_HISTORY_TABLE_NAME
                            + "(" +
                            DatabaseManager.INPUT_HISTORY_NAME + ") VALUES (?)",
                    new Object[]{strInput});
            row = 1;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return row;
    }

    public List<String> getAllInputHistory() {
        List<String> result = new ArrayList<String>();
        try {
            String sql = "SELECT " + DatabaseManager.INPUT_HISTORY_NAME +
                    " FROM " + DatabaseManager.INPUT_HISTORY_TABLE_NAME + " LIMIT " + 0 + "," + 30;
            Cursor cursor = db.rawQuery(sql, null);
            if (cursor != null) {
                int resultCount = cursor.getCount();
                if (resultCount > 0) {
                    cursor.moveToFirst();
                    for (; !cursor.isAfterLast(); cursor.moveToNext()) {
                        int columnIndex = cursor.getColumnIndex(DatabaseManager.INPUT_HISTORY_NAME);
                        String s = cursor.getString(columnIndex).trim();
                        result.add(s);
                    }
                }
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public void deleteAllInputHistor(){
        db.delete(DatabaseManager.INPUT_HISTORY_TABLE_NAME,null,null);
    }
}
